﻿CREATE DATABASE patient_crm DEFAULT charset=utf8;

USE patient_crm;

CREATE TABLE `patients` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PatientName` varchar(100) NOT NULL,
  `NationalCode` varchar(15) DEFAULT NULL,
  `IdNumber` int(11) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `FatherName` varchar(100) DEFAULT NULL,
  `Tel` varchar(20) DEFAULT NULL,
  `EmailAddress` varchar(30) DEFAULT NULL,
  `Address` varchar(100) DEFAULT NULL,
  `Picture` tinyblob,
  `Familiarity` varchar(100) DEFAULT NULL,
  `VIP` tinyint(1) DEFAULT NULL,
  `SubmitDate` datetime NOT NULL,
  `Description` text,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `patient_id` (`ID`)
) ENGINE=InnoDB Default charset=utf8;



CREATE TABLE `questiontypes` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Description` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `questiontype_id` (`ID`)
) ENGINE=InnoDB Default charset=utf8;


CREATE TABLE `questions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `QuestionDesc` text NOT NULL,
  `QuestionTypeId` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `question_id` (`ID`),
  KEY `QuestionTypeId` (`QuestionTypeId`),
  CONSTRAINT `FK23DC49289C340DF1` FOREIGN KEY (`QuestionTypeId`) REFERENCES `questiontypes` (`ID`)
) ENGINE=InnoDB Default charset=utf8;




CREATE TABLE `files` (
  `PatientId` int(11) NOT NULL,
  `FileNumber` varchar(9) NOT NULL,
  `CreateDate` datetime NOT NULL,
  PRIMARY KEY (`PatientId`),
  UNIQUE KEY `PatientId` (`PatientId`),
  KEY `patient_id` (`PatientId`),
  KEY `file_number` (`FileNumber`),
) ENGINE=InnoDB Default charset=utf8;


CREATE TABLE `answers` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `QuestionId` int(11) DEFAULT NULL,
  `FileId` int(11) DEFAULT NULL,
  `AnswerDesc` text NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `answer_id` (`ID`),
  KEY `QuestionId` (`QuestionId`),
  KEY `FileId` (`FileId`),
  CONSTRAINT `FK8848AC761B1063D5` FOREIGN KEY (`FileId`) REFERENCES `files` (`PatientId`),
  CONSTRAINT `FK8848AC7623B84942` FOREIGN KEY (`QuestionId`) REFERENCES `questions` (`ID`)
) ENGINE=InnoDB Default charset=utf8;



CREATE TABLE `visits` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PatientId` int(11) DEFAULT NULL,
  `Description` text NOT NULL,
  `VisitDate` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`),
  KEY `visit_id` (`ID`),
  KEY `PatientId` (`PatientId`),
  CONSTRAINT `FKEBFE4D173FA4DD82` FOREIGN KEY (`PatientId`) REFERENCES `patients` (`ID`)
) ENGINE=InnoDB Default charset=utf8;